package cn.com.utt.unms.mapper;

import java.util.Collection;
import java.util.List;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.util.Map;
import cn.com.utt.unms.pojo.Device;

@Mapper
public interface DeviceMapper {
	@Select("SELECT * FROM device where sn=#{sn}")
	Device queryDeviceBySn(@Param("sn")String sn);
	
	@Select("SELECT * FROM device where security_code=#{securityCode}")
	Device queryDeviceBySecurityCode(@Param("securityCode")String securityCode);
	
	@Select({"<script>",
	    "SELECT *", 
	    "FROM device",
	    "WHERE 1=1 ", 
	    "<if test='networkIds != null and networkIds.size>0'>",
		    "and network_id IN", 
		      "<foreach item='item' index='index' collection='networkIds'",
		        "open='(' separator=',' close=')'>",
		        "#{item}",
		      "</foreach>",
	     "</if>",
	     "<if test='searchs != null'>",
	      "<foreach item='item' index='index' collection='searchs'",
	        "open='and' separator='and' close=''>",
	        "<choose>",
	        "<when test='item.op != \"like\"'>",
	        "${item.column}",
	        " = ",
	        "#{item.value}",
	        "</when>",
	        "<otherwise>",
	        "${item.column}",
	        " like ",
	        "'%${item.value}%'",
	        "</otherwise>",
	        "</choose>",
	      "</foreach>",
	      "</if>",
	      "<if test='orderColumn != null'>",
	      "order by ${orderColumn} ${orderDirection}",
	      "</if>",
	      "limit #{start},#{rows}",
	    "</script>"})
	List<Device> queryByCondition(Map<String, Object> conditions);
	
	@Select({"<script>",
	    "SELECT COUNT(*) AS total", 
	    "FROM device",
	    "WHERE 1=1", 
	    "<if test='networkIds != null and networkIds.size>0'>",
	    "and network_id IN", 
	      "<foreach item='item' index='index' collection='networkIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      "</if>",
	      "<if test='searchs != null'>",
	      "<foreach item='item' index='index' collection='searchs'",
	        "open='and' separator='and' close=''>",
	        "<choose>",
	        "<when test='item.op != \"like\"'>",
	        "${item.column}",
	        " = ",
	        "#{item.value}",
	        "</when>",
	        "<otherwise>",
	        "${item.column}",
	        " like ",
	        "'%${item.value}%'",
	        "</otherwise>",
	        "</choose>",
	      "</foreach>",
	      "</if>",
	    "</script>"})
	int countByCondition(Map<String, Object> conditions);
	
	@Insert("insert into device(network_id,name,sn,code,register_time,bind_time,security_code,is_fast) values(#{networkId},#{name},#{sn},#{code},now(),now(),#{securityCode},#{isFast})")
	@Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")
	void save(Device device);
	
	@Update("update `device` set network_id=#{networkId},name=#{name},auto_upgrade_enable=#{autoUpgradeEnable},rf_id=#{rfId},version=#{version},is_fast=#{isFast} where id=#{id}")
	void update(Device device);
	
	@Select({"<script>",
	    "insert into device(network_id,name,sn,code,register_time,bind_time,security_code) values", 
	      "<foreach item='item' index='index' collection='devices' separator=','>",
	        "(#{item.networkId},#{item.name},#{item.sn},#{item.code},now(),now(),#{item.securityCode})",
	      "</foreach>",
	    "</script>"})
	void batchSave(@Param("devices")Collection<Device> devices);
	
	@Select("select * from device where id=#{deviceid}")
	Device queryDeviceById(@Param("deviceid")Long deviceid);
	
	@Select("select count(*) from device where network_id=#{networkId}")
	int queryCountByNetworkId(@Param("networkId")Long networkId);
	
	@Update({"<script>",
	    "DELETE FROM ", 
	    "device ",
	    "WHERE ", 
	    "id IN", 
	      "<foreach item='item' index='index' collection='devices'",
	        "open='(' separator=',' close=')'>",
	        "#{item.id}",
	      "</foreach>",
	"</script>"})
	void unbind(@Param("devices")List<Device> devices);
	
	@Update({"<script>",
	    "update ", 
	    "device set network_id=#{networkId}",
	    "WHERE ", 
	    "id IN", 
	      "<foreach item='item' index='index' collection='deviceIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	void updateDevicesNetworkId(@Param("deviceIds")List<Long> deviceIds, @Param("networkId")Long networkId);
	
	@Update("update device set network_id=null where network_id=#{networkId}")
	void deleteDevicesNetworkId(@Param("networkId")Long networkId);
	
	@Select({"<script>",
	    "SELECT *", 
	    "FROM device",
	    "WHERE ", 
	    "id IN", 
	      "<foreach item='item' index='index' collection='deviceIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	List<Device> queryDevicesByIds(@Param("deviceIds")Collection<Long> deviceIds);
	
	@Select({"<script>",
	    "SELECT *", 
	    "FROM device",
	    "WHERE ", 
	    "network_id IN", 
	      "<foreach item='item' index='index' collection='networkIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	List<Device> queryDevicesByNetworkIds(@Param("networkIds")List<Long> networkIds);
	
	@Select({"<script>",
	    "SELECT id", 
	    "FROM device",
	    "WHERE ", 
	    "network_id IN", 
	      "<foreach item='item' index='index' collection='networkIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	List<Long> queryDeviceIdsByNetworkIds(@Param("networkIds")List<Long> networkIds);
	
	@Select({"<script>",
	    "SELECT *", 
	    "FROM device",
	    "WHERE ", 
	    "rf_id IN", 
	      "<foreach item='item' index='index' collection='rfIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	List<Device> queryDeviceByRfIds(@Param("rfIds")long []rfIds);
		
	@Select({"<script>",
	    "SELECT `status`,type,count(id) as count", 
	    "FROM device",
	    "WHERE ", 
	    "id IN", 
	      "<foreach item='item' index='index' collection='deviceIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      "group by `status`,type",
	"</script>"})
	List<Map<String, Object>> queryDeviceCountByIds(@Param("deviceIds")long[] deviceIds);
	
	@Select({"<script>",
	    "select count(distinct network_id) as count ", 
	    "FROM device ",
	    "WHERE ", 
	    "network_id IN", 
	      "<foreach item='item' index='index' collection='networkIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	int queryIfHasReference(@Param("networkIds")List<Long> networkIds);
	
	
	@Update({"<script>",
	    "update ", 
	    "device set rf_id=#{rfId}",
	    "WHERE ", 
	    "id IN", 
	      "<foreach item='item' index='index' collection='deviceIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	void updateDeviceRfsById(@Param("deviceIds")List<Long> deviceIds,@Param("rfId")Long rfId);
	
	@Select({"<script>",
	    "select count(id) as count ", 
	    "FROM device ",
	    "WHERE ", 
	    "network_id IN", 
	      "<foreach item='item' index='index' collection='networkIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	int queryCountByNetworkIds(@Param("networkIds")List<Long> networkIds);
	
}
